package com.saishi.dao.DaoImpl;

import com.saishi.dao.YueYonHu;
import com.saishi.entity.*;

import com.saishi.util.AliPool;
import org.apache.commons.dbutils.QueryRunner;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class YueYDaoImpl implements YueYonHu {

    QueryRunner runner = new QueryRunner();

    @Override
    //所有用户学生
    public List<User> syyh(String bj) throws SQLException {

        String sql = " SELECT user.uid,user.uname,user.ugender FROM   classes INNER JOIN  class_user on classes.cid= class_user.classid  INNER JOIN   user on    class_user.userid=`user`.uid \n" +
                "\t\n" +
                "\t\tWHERE   class_user.classid= (SELECT cid FROM classes WHERE  cname=?)";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1,bj);
        List<User> list = new ArrayList<User>();
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {
            int idxs=rs.getInt(1);
            String xsxx=rs.getNString(2);
            String xsxb=rs.getNString(3);

            User lity= new User(idxs,xsxx,xsxb);
            list.add(lity);
        }
        conn.close();
        return list;
    }
    //登入
    @Override
    public User dr(String yhm, String mm) throws SQLException {
        String sql = "SELECT user.uid,user.uname,user.unumber,user.uprofession FROM  user  INNER JOIN  classes on  classes.teacherid=user.uid   WHERE uusername=? and  upass=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1, yhm);
        pt.setString(2, mm);
        User suser=null;
        ResultSet rs = pt.executeQuery();
        if(rs.next()) {

            int yhm1=rs.getInt(1);
            String mm1=rs.getString(2);
            String dh=rs.getString(3);
            String zy=rs.getString(4);
            suser = new User(yhm1,mm1,dh,zy);

        }
        conn.close();
        return  suser;
    }

    @Override
    public List<Classes> lsbjcx(int bj) throws SQLException {
        String sql = "SELECT  cname ,ccreatime FROM classes WHERE  teacherid=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setInt(1,bj);
        List<Classes> list = new ArrayList<>();
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {

            String xsxx=rs.getString(1);
            Date xsxb=rs.getDate(2);
            Classes lity= new Classes(xsxx,xsxb);
            list.add(lity);
        }
        conn.close();
        return list;
    }

    @Override
    public Hrsrqi SJ(String bj) throws SQLException {
        String sql = "SELECT EXTRACT(YEAR FROM ccreatime),year(CURRENT_DATE)  FROM classes WHERE cname=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1,bj);
        Hrsrqi lity=null;
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {

            String xsxx=rs.getString(1);
           String xsxb=rs.getString(2);
            lity= new Hrsrqi(xsxx,xsxb);

        }
        conn.close();
        return  lity;
    }

    @Override
    public Hrsyue SJ1(String bj) throws SQLException {
        String sql = "SELECT MONTH(CURDATE())  FROM classes WHERE cname=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1,bj);
        Hrsyue lity=null;
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {

            int xsxx=rs.getInt(1);

            lity= new Hrsyue(xsxx);

        }
        conn.close();
        return  lity;
    }

    @Override
    public List<HrsBjtxcx> Banjitx(String bj,int xuhao) throws SQLException {
        String sql = "SELECT user.uid,user.uname FROM   user INNER JOIN class_user ON  user.uid=class_user.userid INNER JOIN classes ON class_user.classid=classes.cid WHERE classes.cname=? LIMIT ?,5";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1,bj);
        pt.setInt(2,(xuhao-1) * 4);
        List<HrsBjtxcx> list = new ArrayList<>();
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {

            int seutid=rs.getInt(1);
            String seutname=rs.getString(2);
           HrsBjtxcx lity= new HrsBjtxcx(seutid,seutname);
            list.add(lity);
        }
        conn.close();
        return list;
    }

    @Override
    public int count(String bj) throws SQLException {
        String sql = "SELECT ROUND(count(*)/5) FROM user INNER JOIN class_user ON  user.uid=class_user.userid INNER JOIN classes ON class_user.classid=classes.cid WHERE classes.cname=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);

        pt.setString(1,bj);
        ResultSet rs = pt.executeQuery();

        int ok = 0;
        while (rs.next()) {
            ok = rs.getInt(1);
        }

        return ok;
    }


    @Override
    public List<HRSKaoQinQinKuaN> SKaoQinQinKuaN(String bj, String nian, String yue) throws SQLException {
        String sql = " SELECT  classes.cname, `user`.uid,`user`.uname,task.cause,task.roleid,DAY(task.roleid)\n" +
                "\n" +
                " FROM   classes INNER JOIN  class_user on classes.cid= class_user.classid  INNER JOIN   user on    class_user.userid=`user`.uid \t INNER JOIN   task on class_user.cuid= task.userid  where classes.cname=? and  YEAR(task.roleid)=? and MONTH(task.roleid)=?";
        Connection conn = AliPool.getConn();
        PreparedStatement pt = conn.prepareStatement(sql);
        pt.setString(1,bj);
        pt.setString(2,nian);
        pt.setString(3,yue);

        List<HRSKaoQinQinKuaN> list = new ArrayList<>();
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {

            String classes_cname=rs.getString(1);
            int user_uid=rs.getInt(2);
            String user_uname=rs.getString(3);
            String task_cause=rs.getString(4);
            Date task_roleid= rs.getDate(5);
            String DAY_task_roleid=rs.getString(6);
            HRSKaoQinQinKuaN lity= new HRSKaoQinQinKuaN(classes_cname,user_uid,user_uname,task_cause,task_roleid,DAY_task_roleid);
            list.add(lity);
        }
        conn.close();
        return list;
    }

    @Override
    public int addfind(int userid, String roleid, String cause) throws SQLException {
        String sql = "INSERT INTO task(userid,roleid,cause) VALUES(?,?,?)";
        Connection conn = AliPool.getConn();
        int ok = runner.update(conn,sql,userid,roleid,cause);
        conn.close();
        return ok;
    }

    public static void main(String[] args) throws SQLException {
      YueYonHu styu=new YueYDaoImpl();
//        User st=styu.dr("dre","12345");
//        System.out.println(st);
//       String s=new String("asd");
//        System.out.println(s);
//        List<Classes> lsbjcx12=styu.lsbjcx(3);
//        System.out.println(lsbjcx12);
//        hrsrqi lsbjcx12=styu.SJ("c1806");
//      System.out.println(lsbjcx12);
//        Hrsyue lsbjcx12=styu.SJ1("c1806");
//     System.out.println(lsbjcx12);
//        List<HrsBjtxcx> lsbjcx12=styu.Banjitx("c1806",2);
//       System.out.println(lsbjcx12);
//        int st1=styu.count("c1806");
//        System.out.println(st1);
//        List<HRSKaoQinQinKuaN> HRSKaoQinQinKuaN=styu.SKaoQinQinKuaN("c1806","2021","6");
//        System.out.println(HRSKaoQinQinKuaN);
       int yy = styu.addfind(1,"2020-1-1","sdf");
        System.out.println(yy);
    }



}
